﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;

namespace Zyg.Utility.DBUtility
{
    public class SqlServerHelper
    {
        #region Fields

        private static string connectionString;

        #endregion

        #region Constructor

        static SqlServerHelper()
        {
            connectionString = Zyg.SSO.Configuration.ConfigurationManager.Providers.DALProviders.Default.ConnectionString;
        }

        protected SqlServerHelper()
        { }

        #endregion

        /// <summary>
        /// 执行普通的Update,Insert,Delete语句，返回受影响的行数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql)
        {
            int recordsAffected;
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(sql, connection);
                    command.CommandType = CommandType.Text;
                    command.Connection.Open();

                    recordsAffected = command.ExecuteNonQuery();

                    command.Connection.Close();
                }
            }
            catch (SqlException e1)
            {
                throw e1;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return recordsAffected;
        }

        /// <summary>
        /// 执行普通的带参数的Update,Insert,Delete语句，返回受影响的行数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            int recordsAffected;
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(sql, connection);
                    command.CommandType = CommandType.Text;
                    command.Parameters.AddRange(parameters);
                    command.Connection.Open();

                    recordsAffected = command.ExecuteNonQuery();

                    command.Connection.Close();
                }
            }
            catch (SqlException e1)
            {
                throw e1;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return recordsAffected;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteStoredProcedure(string storedProcedureName)
        {
            int recordsAffected;
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(storedProcedureName, connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Connection.Open();

                    recordsAffected = command.ExecuteNonQuery();

                    command.Connection.Close();
                }
            }
            catch (SqlException e1)
            {
                throw e1;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return recordsAffected;
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteStoredProcedure(string storedProcedureName, params SqlParameter[] parameters)
        {
            int recordsAffected;
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(storedProcedureName, connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.AddRange(parameters);

                    command.Connection.Open();

                    recordsAffected = command.ExecuteNonQuery();

                    command.Connection.Close();
                }
            }
            catch (SqlException e1)
            {
                throw e1;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return recordsAffected;
        }

        /// <summary>
        /// 执行查询，返回结果集中第一行第一列的数值，用于执行如select count(*) from TableName之类的语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteScalar(string sql)
        {
            int count;
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(sql, connection);
                    command.CommandType = CommandType.Text;
                    command.Connection.Open();

                    count = int.Parse(command.ExecuteScalar().ToString());

                    command.Connection.Close();
                }
            }
            catch (SqlException e1)
            {
                throw e1;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return count;
        }

        /// <summary>
        /// 执行查询，返回结果集中第一行第一列的数值，用于执行如select count(*) from TableName之类的语句
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            int count;
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlCommand command = new SqlCommand(sql, connection);
                    command.CommandType = CommandType.Text;
                    command.Parameters.AddRange(parameters);
                    command.Connection.Open();

                    count = int.Parse(command.ExecuteScalar().ToString());

                    command.Connection.Close();
                }
            }
            catch (SqlException e1)
            {
                throw e1;
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return count;
        }

        /// <summary>
        /// 普通SQL语句的查询
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <returns>查询结果集</returns>
        public static DataSet Query(string sql)
        {
            try
            {
                DataSet ds = new DataSet();

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(sql, connection);
                    connection.Open();

                    adapter.Fill(ds);

                    connection.Close();
                }
                return ds;
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 普通SQL语句的查询
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="name">DataSet 的名称</param>
        /// <returns>查询结果集</returns>
        public static DataSet Query(string sql, string name)
        {
            try
            {
                DataSet ds = new DataSet(name);

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(sql, connection);
                    connection.Open();

                    adapter.Fill(ds);

                    connection.Close();
                }
                return ds;
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 带参数的普通SQL语句的查询
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="parameteres">参数</param>
        /// <returns>查询结果集</returns>
        public static DataSet Query(string sql, params SqlParameter[] parameteres)
        {
            try
            {
                DataSet ds = new DataSet();

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(sql, connection);
                    adapter.SelectCommand.Parameters.AddRange(parameteres);
                    connection.Open();

                    adapter.Fill(ds);

                    connection.Close();
                }
                return ds;
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 带参数的普通SQL语句的查询
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="name">DataSet 的名称</param>
        /// <param name="parameteres">参数数组</param>
        /// <returns>查询结果集</returns>
        public static DataSet Query(string sql, string name, params SqlParameter[] parameteres)
        {
            try
            {
                DataSet ds = new DataSet(name);

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(sql, connection);
                    adapter.SelectCommand.Parameters.AddRange(parameteres);
                    connection.Open();

                    adapter.Fill(ds);

                    connection.Close();
                }
                return ds;
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 带参数的普通SQL语句的查询，返回从起始位置起的特定条数量
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="startPos">起始位置</param>
        /// <param name="count">数据条数</param>
        /// <param name="name">数据集名称</param>
        /// <returns>查询结果集</returns>
        public static DataSet Query(string sql, int startPos, int count, string name)
        {
            try
            {
                DataSet ds = new DataSet(name);

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(sql, connection);
                    connection.Open();

                    adapter.Fill(ds, startPos, count, name);

                    connection.Close();
                }
                return ds;
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 带参数的普通SQL语句的查询，返回从起始位置起的特定条数量
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="startPos">起始位置</param>
        /// <param name="count">数据条数</param>
        /// <param name="name">数据集名称</param>
        /// <param name="parameteres">查询参数数组</param>
        /// <returns>查询结果集</returns>
        public static DataSet Query(string sql, int startPos, int count, string name, params SqlParameter[] parameteres)
        {
            try
            {
                DataSet ds = new DataSet();

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(sql, connection);
                    adapter.SelectCommand.Parameters.AddRange(parameteres);
                    connection.Open();

                    adapter.Fill(ds, startPos, count, name);

                    connection.Close();
                }
                return ds;
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行存储过程进行查询
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <returns>查询结果集</returns>
        public static DataSet QueryStoredProcedure(string storedProcedureName)
        {
            try
            {
                DataSet ds = new DataSet();

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(storedProcedureName, connection);
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    connection.Open();

                    adapter.Fill(ds);

                    connection.Close();
                }
                return ds;
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行存储过程进行查询
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="name">返回的数据集的名称</param>
        /// <returns>查询结果集</returns>
        public static DataSet QueryStoredProcedure(string storedProcedureName, string name)
        {
            try
            {
                DataSet ds = new DataSet(name);

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(storedProcedureName, connection);
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    connection.Open();

                    adapter.Fill(ds);

                    connection.Close();
                }
                return ds;
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行存储过程进行查询
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>查询结果集</returns>
        public static DataSet QueryStoredProcedure(string storedProcedureName, params SqlParameter[] parameters)
        {
            try
            {
                DataSet ds = new DataSet();

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(storedProcedureName, connection);
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    adapter.SelectCommand.Parameters.AddRange(parameters);

                    connection.Open();

                    adapter.Fill(ds);

                    connection.Close();
                }
                return ds;
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行存储过程进行查询
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="name">返回的数据集的名称</param>
        /// <param name="parameters">参数数组</param>
        /// <returns>查询结果集</returns>
        public static DataSet QueryStoredProcedure(string storedProcedureName, string name, params SqlParameter[] parameters)
        {
            try
            {
                DataSet ds = new DataSet(name);

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(storedProcedureName, connection);
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    adapter.SelectCommand.Parameters.AddRange(parameters);

                    connection.Open();

                    adapter.Fill(ds);

                    connection.Close();
                }
                return ds;
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行存储过程进行查询
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="startPos">起始位置</param>
        /// <param name="count">数据条数</param>
        /// <param name="name">返回的数据集的名称</param>
        /// <returns>查询结果集</returns>
        public static DataSet QueryStoredProcedure(string storedProcedureName, int startPos, int count, string name)
        {
            try
            {
                DataSet ds = new DataSet(name);

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(storedProcedureName, connection);
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    connection.Open();

                    adapter.Fill(ds, startPos, count, name);

                    connection.Close();
                }
                return ds;
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 执行存储过程进行查询
        /// </summary>
        /// <param name="storedProcedureName">存储过程名称</param>
        /// <param name="startPos">起始位置</param>
        /// <param name="count">数据条数</param>
        /// <param name="name">返回的数据集的名称</param>
        /// <returns>查询结果集</returns>
        public static DataSet QueryStoredProcedure(string storedProcedureName, int startPos, int count, string name, SqlParameter[] paramerters)
        {
            try
            {
                DataSet ds = new DataSet(name);

                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    SqlDataAdapter adapter = new SqlDataAdapter();
                    adapter.SelectCommand = new SqlCommand(storedProcedureName, connection);
                    adapter.SelectCommand.Parameters.AddRange(paramerters);
                    adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                    connection.Open();

                    adapter.Fill(ds, startPos, count, name);

                    connection.Close();
                }
                return ds;
            }
            catch (SqlException e)
            {
                throw e;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
